<?php
include "header.inc";

require_once("DataBase.inc");

function norm($str) {
	return str_replace("  "," ",trim($str));
}

if (isset($_FILES['file'])) {
	$error = $_FILES['file']['error'];
	if ($error <> 0) {
		echo("An error occured while uploading the file: ");
		switch ($error) {
			case 1: die("File is too big (more than ".ini_get("upload_max_filesize")."). Please check with administrator to increase this limit if necessary.");
			case 3: die("File has been only partially uploaded. Please retry.");
			case 4: die("No file was uploaded.");
			default: die("Error #".$error.". Please check with administrator to resolve this issue.");
		}
	}
	$filename = $_FILES['file']['tmp_name'];

	echo "Importing excel file ".$_FILES['file']['name']."<br/>";
	echo "Please review the results below, and confirm the import if data is correct:<br/><br/>";
	flush();
	require_once 'Excel/reader.php';

	$excel = new Spreadsheet_Excel_Reader();
	$excel->setOutputEncoding('UTF-8');
	$excel->read($filename);

	$sheet = $excel->sheets[0];
	$cells = $sheet['cells'];
	$nbCols = $sheet['numCols'];
	$nbRows = $sheet['numRows'];
 	$cols = array(
		"Candidate's last name"=>-1,
		"Candidate's first name"=>-1,
		"Khmer Name"=>-1,
		"Sex"=>-1,
		"Date of Birth"=>-1,
                "Age"=>-1,
		"Origin of The Students"=>-1,
		"Origin of The Parents"=>-1,
		"Exam Center"=>-1,
		"Province"=>-1,
		"Highschool Name"=>-1,
 		"NGO following the students"=>-1,
 		"Family Revenue \nper month"=>-1,
 		"Family Expense\nper month"=>-1,
 		"Numbers of member"=>-1,
 		"Numbers of farming land owned"=>-1,
 		"Properties owned:\nCar -Truck - Motor"=>-1,
 		"Numbers of animal owned: Cow - Buffalo - Pig"=>-1,
 		"Comment"=>-1,
	);
 	$cols_mandatory = array(
		"Candidate's last name",
		"Candidate's first name",
		"Khmer Name",
		"Sex",
		"Date of Birth",
                "Age",
		"Origin of The Students",
		"Origin of The Parents",
		"Exam Center",
		"Province",
	);

	$firstRow = 1;
	while ($firstRow < $nbRows) {
		$found = false;
	 	for ($col = 1; $col <= $nbCols; $col++) {
	 		if (!isset($cells[$firstRow][$col])) continue;
	 		$content = @$cells[$firstRow][$col];
	 		if ($content <> null && $content <> "") { $found = true; break; }
	 	}
	 	if ($found) break;
	 	$firstRow++;
	}
	for ($col = 1; $col <= $nbCols; $col++) {
 		if (!isset($cells[$firstRow][$col])) continue;
 		$title = $cells[$firstRow][$col];
 		$found = false;
 		foreach ($cols as $name=>$num)
 			if ($title == $name) {
 				$cols[$name] = $col;
 				$found = true;
 				break;
 			}
 		if (!$found)
 			echo "Warning: column '".$title."' will be ignored.<br/>";
 	}
 	$error = false;
 	foreach ($cols as $name=>$col)
 		if ($col == -1) {
 			echo "<span style='color:red'>Missing column: ".$name."</span><br/>";
 			if (in_array($name, $cols_mandatory))
 				$error = true;
 		}
 	if ($error) {
 		die("Due to previous errors, we cannot import this file. Please correct the excel file and try again.");
 	}

 	$import = array();
	for ($row = $firstRow+1; $row < $nbRows; $row++) {
		$first_name = @$cells[$row][$cols["Candidate's first name"]];
		$last_name = @norm($cells[$row][$cols["Candidate's last name"]]);
		$khmer_name = @$cells[$row][$cols["Khmer Name"]];
		$sex = @norm($cells[$row][$cols["Sex"]]);
		if ($sex <> null) {
			if (strlen($sex)>1) $sex = $sex[0];
			$sex = strtoupper($sex);
		}
		$date_of_birth = @$cells[$row][$cols["Date of Birth"]];
                $age = @$cells[$row][$cols["Age"]];
		$origin_student = @norm($cells[$row][$cols["Origin of The Students"]]);
		$origin_parents = @norm($cells[$row][$cols["Origin of The Parents"]]);
		$high_school = @norm($cells[$row][$cols["Highschool Name"]]);
		$province = @norm($cells[$row][$cols["Province"]]);
		$exam_center = @norm($cells[$row][$cols["Exam Center"]]);
		$ngo = @norm($cells[$row][$cols["NGO following the students"]]);
		$revenue = @norm($cells[$row][$cols["Family Revenue \nper month"]]);
		$expense = @norm($cells[$row][$cols["Family Expense\nper month"]]);
		$members = @norm($cells[$row][$cols["Numbers of member"]]);
		$lands = @norm($cells[$row][$cols["Numbers of farming land owned"]]);
		$properties = @norm($cells[$row][$cols["Properties owned:\nCar -Truck - Motor"]]);
		$animals = @norm($cells[$row][$cols["Numbers of animal owned: Cow - Buffalo - Pig"]]);
		$comment = @norm($cells[$row][$cols["Comment"]]);

		if ($first_name == null && $last_name == null && $khmer_name == null) continue;
		$timestamp_start = mktime(0,0,0,1,0,1902)-(2*365+1)*24*60*60; // calculation due to Excel date format
		$timestamp = $timestamp_start + $date_of_birth*24*60*60;
		$date_of_birth = date("Y-m-d", $timestamp);
		array_push($import,
			array(
				$first_name,$last_name,$khmer_name,$sex,$date_of_birth,$age,
				$origin_student,$origin_parents,$high_school,$province,$exam_center,$ngo,
				$revenue,$expense,$members,$lands,$properties,$animals,$comment
			));
	}
	$all = DataBase::query("SELECT * FROM applicant WHERE selection_campaign_id=".$_SESSION["campaign_id"]);
	$existings = array();
	foreach ($import as $new) {
		foreach ($all as $old) {
			if (($old["applicant_firstname"] == $new[0] && $old["applicant_lastname"] == $new[1]) || $old["applicant_khmer_name"] == $new[2]) {
				array_push($existings, array($new,$old));
				break;
			}
		}
	}
	if (count($existings)>0) {
		echo "<div style='background-color:#FFA0A0;border: 1px solid black'>";
		echo "Some applicants seem to be already present in the database. Here is the list:<br/>";
		foreach ($existings as $e) {
			echo "<table style='border-bottom:1px solid black' cellspacing=0 cellpadding=0><tr><td>The new applicant with first name</td><td> <b>".$e[0][0]."</b></td><td>, last name <b>".$e[0][1]."</b></td><td> and khmer name <b>".$e[0][2]."</b></td></tr>";
			echo "<tr><td>Seems to be the same as existing applicant with first name</td><td> <b>".$e[1]["applicant_firstname"]."</b></td><td>, last name <b>".$e[1]["applicant_lastname"]."</b></td><td> and khmer name <b>".$e[1]["applicant_khmer_name"]."</b></td></tr></table>";
		}
		echo "<br/>";
		echo "<div style='font-size:12pt;font-weight:bold'>";
		echo "If you confirm the import, all new applicants will be created in the database, so we may have duplicate.<br/>";
		echo "If you want to remove some of them, please edit the excel file, remove them, and try again to import.";
		echo "</div>";
		echo "</div>";
		echo "<br/>";
	}
	echo count($import)." applicant(s) found in the Excel file:<br/>";
	echo "<table border=1 style='border-collapse:collapse'>";
	echo "<tr>";
	echo "<th>First Name</th>";
	echo "<th>Last Name</th>";
	echo "<th>Khmer Name</th>";
	echo "<th>Sex</th>";
	echo "<th>Birth date</th>";
        echo "<th>Age</th>";
	echo "<th>Origin of student</th>";
	echo "<th>Origin of parents</th>";
	echo "<th>Highschool</th>";
	echo "<th>Province</th>";
	echo "<th>Exam Center</th>";
	echo "<th>NGO</th>";
	echo "<th>Revenue/month</th>";
	echo "<th>Expense/month</th>";
	echo "<th>Members</th>";
	echo "<th>Lands</th>";
	echo "<th>Properties</th>";
	echo "<th>Animals</th>";
	echo "<th>Comment</th>";
	echo "</tr>";
	foreach ($import as $applicant) {
		$first_name = $applicant[0];
		$last_name = $applicant[1];
		$khmer_name = $applicant[2];
		$sex = $applicant[3];
		$date_of_birth = $applicant[4];
                $age = $applicant[5];
		$origin_student = $applicant[6];
		$origin_parents = $applicant[7];
		$high_school = $applicant[8];
		$province = $applicant[9];
		$exam_center = $applicant[10];
		$ngo = $applicant[11];
		$revenue = $applicant[12];
		$expense = $applicant[13];
		$members = $applicant[14];
		$lands = $applicant[14];
		$properties = $applicant[16];
		$animals = $applicant[17];
		$comment = $applicant[18];
		echo "<tr>";
		if ($first_name == null || strlen(trim($first_name))==0) {
			echo "<td style='color:red;font-weight:bold;background-color:#606060'>MISSING</td>";
		} else {
			$first_name = trim($first_name);
			echo "<td nowrap='nowrap'>".$first_name."</td>";
		}
		if ($last_name == null || strlen($last_name)==0)
			echo "<td style='color:red;font-weight:bold;background-color:#606060'>MISSING</td>";
		else
			echo "<td nowrap='nowrap'>".$last_name."</td>";
		if ($khmer_name == null || strlen($khmer_name)==0)
			echo "<td style='color:red;font-weight:bold;background-color:#606060'>MISSING</td>";
		else
			echo "<td nowrap='nowrap'>".$khmer_name."</td>";
		if ($sex == null || strlen($sex)==0)
			echo "<td style='color:red;font-weight:bold;background-color:#606060'>MISSING</td>";
		else if ($sex <> "M" && $sex <> "F")
			echo "<td style='color:red;font-weight:bold;background-color:#606060'>INVALID:".$sex."</td>";
		else
			echo "<td nowrap='nowrap'>".$sex."</td>";
		if ($date_of_birth == null || strlen($date_of_birth)==0)
			echo "<td style='color:red;font-weight:bold;background-color:#606060'>MISSING</td>";
		else
			echo "<td nowrap='nowrap'>".$date_of_birth."</td>";

                if ($age == null || strlen($age)==0)
			echo "<td style='color:red;font-weight:bold;background-color:#606060'>MISSING</td>";
		else
			echo "<td nowrap='nowrap'>".$age."</td>";

		if ($origin_student == null || strlen($origin_student)==0)
			echo "<td style='color:red;font-weight:bold;background-color:#606060'>MISSING</td>";
		else
			echo "<td nowrap='nowrap'>".$origin_student."</td>";
		if ($origin_parents == null || strlen($origin_parents)==0)
			echo "<td style='color:red;font-weight:bold;background-color:#606060'>MISSING</td>";
		else
			echo "<td nowrap='nowrap'>".$origin_parents."</td>";
		if ($high_school == null || strlen($high_school)==0)
			echo "<td style='color:red;font-weight:bold;background-color:#606060'>MISSING</td>";
		else
			echo "<td nowrap='nowrap'>".$high_school."</td>";
		if ($province == null || strlen($province)==0)
			echo "<td style='color:red;font-weight:bold;background-color:#606060'>MISSING</td>";
		else
			echo "<td nowrap='nowrap'>".$province."</td>";
		if ($exam_center == null || strlen($exam_center)==0)
			echo "<td style='color:red;font-weight:bold;background-color:#606060'>MISSING</td>";
		else
			echo "<td nowrap='nowrap'>".$exam_center."</td>";
		echo "<td nowrap='nowrap'>".$ngo."</td>";
		echo "<td nowrap='nowrap'>".($revenue<>null ? $revenue : "")."</td>";
		echo "<td nowrap='nowrap'>".($expense<>null ? $expense : "")."</td>";
		echo "<td nowrap='nowrap'>".($members<>null ? $members : "")."</td>";
		echo "<td nowrap='nowrap'>".($lands<>null ? $lands : "")."</td>";
		echo "<td nowrap='nowrap'>".($properties<>null ? $properties : "")."</td>";
		echo "<td nowrap='nowrap'>".($animals<>null ? $animals : "")."</td>";
		echo "<td nowrap='nowrap'>".($comment<>null ? $comment : "")."</td>";
	}
	echo "</table>";
	echo "<form name='import' action='' method='post'>";
	echo "<input type='hidden' name='confirm_import' value='yes'/>";
	echo "<button type='submit'>Confirm Import</button>";
	echo "</form>";
	$_SESSION["import_result"] = $import;
	die();
} else if (isset($_POST["confirm_import"])) {

	$now = time();
	DataBase::insert("concurrency",array("operation"=>"import_deo","timestamp"=>$now,"username"=>$_SESSION["user"]));
	$res = DataBase::query("SELECT * FROM concurrency WHERE operation='import_deo' AND username<>'".$_SESSION["user"]."'");
	if ($res <> false && count($res)>0) {
		DataBase::delete("concurrency",array("operation"=>"import_deo","username"=>$_SESSION["user"]));
		echo "User ".$res[0]["username"]." is currently importing applicants at the same as you!<br/>";
		echo "Only one import can be done at the same time to avoid duplicates and errors while affecting applicant ID.<br/>";
		echo "Please wait a few seconds an try again.<br/>";
		echo "<form name='import' action='' method='post'>";
		echo "<input type='hidden' name='confirm_import' value='yes'/>";
		echo "<button type='submit'>Confirm Import</button>";
		echo "</form>";
		die();
	}

	$programs = DataBase::query("SELECT program.* FROM selection_campaign_programs LEFT JOIN program ON program.program_id=selection_campaign_programs.program_id WHERE selection_campaign_id=".$_SESSION["campaign_id"]);

 	$provinces = array();
 	$result = DataBase::select("province", null, null);
 	if ($result <> false)
 		foreach ($result as $r)
 			$provinces[$r["province_name"]] = $r["province_id"];

 	$high_schools = array();
 	$result = DataBase::select("high_school", null, null);
 	if ($result <> false)
 		foreach ($result as $r)
 			$high_schools[$r["high_school_name"]] = $r["high_school_id"];

 	$exam_centers = DataBase::select("exam_center", null, array("selection_campaign_id"=>$_SESSION["campaign_id"]));
 	if ($exam_centers == false) $exam_centers = array();

 	$ngos = array();
 	$result = DataBase::select("ngo", null, null);
 	if ($result <> false)
 		foreach ($result as $r)
 			$ngos[$r["ngo_name"]] = $r["ngo_id"];

 	$errors = "";

 	function check_error() {
 		global $errors;
		if (DataBase::last_error_number() <> 0)
			$errors .= "Error #".DataBase::last_error_number().": ".DataBase::last_error_message()."<br/>";
 	}

 	$rows = $_SESSION["import_result"];
	unset($_SESSION["import_result"]);

	$res = DataBase::query("SELECT COUNT(applicant_selection_id) FROM applicant WHERE selection_campaign_id='".$_SESSION["campaign_id"]."'");
	$count = $res <> false && count($res)>0 ? $res[0][0] : 0;
	$last_selection_id = 0;
	if ($count > 0) {
		$res = DataBase::query("SELECT applicant_selection_id FROM applicant WHERE selection_campaign_id='".$_SESSION["campaign_id"]."' ORDER BY applicant_selection_id DESC LIMIT 0,1");
		if ($res<>false && count($res)>0) {
			$s = $res[0][0];
			if ($s <> null && strlen($s)>0) $last_selection_id = intval($s);
		}
	}

 	foreach ($rows as $row) {
 		$first_name = $row[0];
 		$last_name = $row[1];
 		$khmer_name = $row[2];
 		$sex = $row[3];
 		if ($sex <> null && $sex <> "M" && $sex <> "F") $sex = null;
 		$date_of_birth = $row[4];
                $age = $row[5];
		$origin_student = $row[6];
		$origin_parents = $row[7];
		$high_school = $row[8];
		$province = $row[9];
		$exam_center = $row[10];
		$ngo = $row[11];
		$revenue = $row[12];
		$expense = $row[13];
		$members = $row[14];
		$lands = $row[15];
		$properties = $row[16];
		$animals = $row[17];
		$comment = $row[18];

 		if ($province <> null && strlen(trim($province))>0) {
 			$province = trim($province);
 			if (isset($provinces[$province]))
 				$province_id = $provinces[$province];
 			else {
 				$province_id = DataBase::insert("province", array("province_name"=>$province));
 				$provinces[$province] = $province_id;
 				check_error();
 			}
 		} else
 			$province_id = null;

 		$exam_center_id = null;
 		if ($exam_center <> null && strlen(trim($exam_center))>0) {
 			$exam_center = trim($exam_center);
 			foreach ($exam_centers as $center) {
 				if ($center["exam_center_name"] == $exam_center && $center["province_id"] == $province_id) {
 					$exam_center_id = $center["exam_center_id"];
 					break;
 				}
 			}
 			if ($exam_center_id == null) {
 				$exam_center_id = DataBase::insert("exam_center", array("exam_center_name"=>$exam_center,"province_id"=>$province_id,"selection_campaign_id"=>$_SESSION["campaign_id"]));
 				array_push($exam_centers, array("exam_center_name"=>$exam_center,"province_id"=>$province_id,"exam_center_id"=>$exam_center_id,"selection_campaign_id"=>$_SESSION["campaign_id"]));
 				check_error();
 			}
 		}

 		$high_school_id = null;
 		if ($high_school <> null && strlen(trim($high_school))>0) {
 			$high_school = trim($high_school);
 			if (isset($high_schools[$high_school]))
 				$high_school_id = $high_schools[$high_school];
 			else {
 				$high_school_id = DataBase::insert("high_school", array("high_school_name"=>$high_school));
 				$high_schools[$high_school] = $high_school_id;
 				check_error();
 			}
  		}

 		$origin_student_id = null;
 		if ($origin_student <> null && strlen(trim($origin_student))>0) {
 			$origin_student = trim($origin_student);
 			if (isset($provinces[$origin_student]))
 				$origin_student_id = $provinces[$origin_student];
 			else {
 				$origin_student_id = DataBase::insert("province", array("province_name"=>$origin_student));
 				$provinces[$origin_student] = $origin_student_id;
 				check_error();
 			}
 		}

 		$origin_parents_id = null;
 		if ($origin_parents <> null && strlen(trim($origin_parents))>0) {
 			$origin_parents = trim($origin_parents);
 			if (isset($provinces[$origin_parents]))
 				$origin_parents_id = $provinces[$origin_parents];
 			else {
 				$origin_parents_id = DataBase::insert("province", array("province_name"=>$origin_parents));
 				$provinces[$origin_parents] = $origin_parents_id;
 				check_error();
 			}
 		}

 		if ($ngo <> null && strlen(trim($ngo))>0) {
 			$ngo = trim($ngo);
 			if (isset($ngos[$ngo]))
 				$ngo_id = $ngos[$ngo];
 			else {
 				$ngo_id = DataBase::insert("ngo", array("ngo_name"=>$ngo));
 				$ngos[$ngo] = $ngo_id;
 				check_error();
 			}
 		} else
 			$ngo_id = null;

		$applicant_selection_id = str_pad(++$last_selection_id,4,"0",STR_PAD_LEFT);

 		$applicant_id = DataBase::insert("applicant", array(
 			"applicant_firstname"=>$first_name,
			"applicant_lastname"=>$last_name,
			"applicant_khmer_name"=>$khmer_name,
			"sex"=>$sex,
			"applicant_origin_province_id"=>$origin_student_id,
			"applicant_parents_province_id"=>$origin_parents_id,
			"ngo_id"=>$ngo_id,
			"applicant_date_birth"=>$date_of_birth,
                        "applicant_age"=>$age,
			"high_school_id"=>$high_school_id,
 			"selection_campaign_id"=>$_SESSION["campaign_id"],
			"applicant_selection_id"=>$applicant_selection_id,
 			"comments"=>$comment,
		));
		check_error();
		if (DataBase::last_error_number() == 0 && $applicant_id <> 0) {
			DataBase::insert("applicant_exam_room_beta", array("applicant_id"=>$applicant_id,"exam_center_id"=>$exam_center_id));
			check_error();
			foreach ($programs as $program) {
				DataBase::insert("applicant_interest",array("applicant_id"=>$applicant_id,"program_id"=>$program["program_id"],"interested"=>1));
				check_error();
			}
			DataBase::insert("applicant_more_info",array(
				"applicant_id"=>$applicant_id,
				"applicant_more_info_attendee_exam_status"=>"0",
		 		"family_revenue"=>$revenue,
		 		"family_expense"=>$expense,
		 		"family_members"=>$members,
		 		"farming_land_owned"=>$lands,
		 		"properties_owned"=>$properties,
		 		"animal_owned"=>$animals,
			));
			check_error();
		}
 	}
 	if (strlen($errors)==0)
 		echo("Applicants successfully imported.<br/>");
 	else
 		echo $errors;
	DataBase::delete("concurrency",array("operation"=>"import_deo","username"=>$_SESSION["user"]));
 	echo "<a href='check_typing.php'>Check if there is no typing mistakes</a><br/>";
 	echo "<a href='applicants.php?view=from_data_entry_operators'>View list of applicants</a><br/>";
 	echo "<a href='import_data_entry_operator_file.php'>Import another file</a><br/>";
} else {
?>
<form name='upload' method='post' enctype="multipart/form-data">
Select the Excel file to import: <input type='file' name='file'/><br/>
<button type='submit'>Import</button>
</form>
<?php
}
include "footer.inc";
?>